﻿CREATE PROCEDURE LOGIN

	(
	@P_TAI_KHOAN NVARCHAR(255),
	@P_MAT_KHAU NVARCHAR(255)
	)
AS
BEGIN
	DECLARE @P_COUNT INT, @P_QUYEN_HAN NVARCHAR(1), @KET_QUA INT
	SELECT @P_COUNT = COUNT(*) FROM USERS WHERE taikhoan_nv=@P_TAI_KHOAN AND matkhau_nv=@P_MAT_KHAU;
	IF @P_COUNT > 0
		BEGIN
			SELECT @P_QUYEN_HAN = quyen_nv FROM USERS WHERE taikhoan_nv=@P_TAI_KHOAN AND matkhau_nv=@P_MAT_KHAU;
			IF (@P_QUYEN_HAN = 'A')
				SET @KET_QUA = 1;
			ELSE IF (@P_QUYEN_HAN = 'Q')
				SET @KET_QUA = 2;
			ELSE
				SET @KET_QUA = 3;
		END
	ELSE
		SET @KET_QUA = 0;
	SELECT @KET_QUA AS KQ;
END

/**************************************
***************************************/

CREATE PROCEDURE UPDATE_MAT_KHAU
	(
	@P_TAI_KHOAN NVARCHAR(50),
	@P_MAT_KHAU_MOI NVARCHAR(255)
	)
AS
BEGIN
	UPDATE USERS SET
	matkhau_nv=@P_MAT_KHAU_MOI
	WHERE LOWER(taikhoan_nv)=@P_TAI_KHOAN;
END

/**************************************
***************************************/

CREATE PROCEDURE dbo.DS_NHAN_VIEN
AS
BEGIN
	SELECT * FROM USERS;
END

/**************************************
***************************************/

CREATE PROCEDURE KT_NGUOIDUNG

	(
	@P_TAI_KHOAN NVARCHAR(255)
	)
AS
BEGIN
	DECLARE @P_COUNT INT, @KET_QUA INT
	SELECT @P_COUNT = COUNT(*) FROM USERS WHERE taikhoan_nv=@P_TAI_KHOAN;
	IF @P_COUNT > 0
		BEGIN
			SET @KET_QUA = 1;
		END
	ELSE
		SET @KET_QUA = 0;
	SELECT @KET_QUA AS KQ;
END

/**************************************
***************************************/

CREATE PROCEDURE THEM_NHAN_VIEN
	(
	@P_TAI_KHOAN NVARCHAR(50),
	@P_MAT_KHAU NVARCHAR(255),
	@P_QUYEN_HAN NVARCHAR(1),
	@P_NGAY_SINH DATETIME,
	@P_SDT NVARCHAR(25),
	@P_NGAY_DK DATETIME
	)
AS
BEGIN
	INSERT INTO USERS (taikhoan_nv, matkhau_nv, quyen_nv, ngaysinh_nv, sdt_nv, ngay_dk) 
	values(@P_TAI_KHOAN, @P_MAT_KHAU, @P_QUYEN_HAN, @P_NGAY_SINH, @P_SDT, @P_NGAY_DK);
END

/**************************************
***************************************/

CREATE PROCEDURE XOA_NHAN_VIEN
	(
	@P_ID INT
	)
AS
BEGIN
	DELETE FROM USERS
WHERE ID = @P_ID;
END

/**************************************
***************************************/

CREATE PROCEDURE dbo.KQ_NHAN_VIEN_BY_TAI_KHOAN
(
	@P_TAI_KHOAN NVARCHAR(50)
)
AS
BEGIN
	SELECT * FROM USERS WHERE taikhoan_nv = @P_TAI_KHOAN;
END

/**************************************
***************************************/

CREATE PROCEDURE SUA_NHAN_VIEN
(
	@P_ID INT,
	@P_TAI_KHOAN NVARCHAR(50),
	@P_MAT_KHAU NVARCHAR(255),
	@P_QUYEN_HAN NVARCHAR(1),
	@P_NGAY_SINH DATETIME,
	@P_SDT NVARCHAR(25),
	@P_NGAY_DK DATETIME
)
AS
BEGIN
	UPDATE USERS SET TAIKHOAN_NV = @P_TAI_KHOAN, MATKHAU_NV = @P_MAT_KHAU, QUYEN_NV = @P_QUYEN_HAN, NGAYSINH_NV = @P_NGAY_SINH, SDT_NV = @P_SDT, NGAY_DK = @P_NGAY_DK
	WHERE ID = @P_ID;
END

/**************************************
***************************************/

CREATE PROCEDURE dbo.KQ_NHAN_VIEN
(
	@P_NV_ID INT
)
AS
BEGIN
	SELECT * FROM USERS WHERE ID = @P_NV_ID;
END

/**************************************
***************************************/
CREATE PROCEDURE REPORT_KIEU_CHUYEN_PHAT
(
	@P_STR NVARCHAR (1000)
)
AS
BEGIN
	DECLARE @SQL NVARCHAR(3250)
	SET @SQL =
	'SELECT HD.*, SP.TEN_SP, HS.SOLUONG_DH, US.TAIKHOAN_NV, U.TAIKHOAN_NV AS "NGUOILAP"
	FROM HOADON HD LEFT JOIN HOADON_SANPHAM HS ON HD.MA_HD=HS.MA_HD LEFT JOIN SANPHAM SP ON HS.MA_SP = SP.MA_SP LEFT JOIN USERS US ON US.ID = HD.NHANVIEN_GH LEFT JOIN USERS U ON U.ID = HD.NGUOILAP_HD
	WHERE ' + @P_STR;
END
EXEC SP_EXECUTESQL @SQL

/**************************************
***************************************/
CREATE PROCEDURE REPORT_THEO_NGAY
	(
	@P_STR NVARCHAR (1000)
	)
AS
BEGIN
	DECLARE @SQL NVARCHAR(3250)
	SET @SQL =
	'SELECT HD.*, SP.TEN_SP, HS.SOLUONG_DH, US.TAIKHOAN_NV, U.TAIKHOAN_NV AS "NGUOILAP"
	FROM HOADON HD LEFT JOIN HOADON_SANPHAM HS ON HD.MA_HD=HS.MA_HD LEFT JOIN SANPHAM SP ON HS.MA_SP = SP.MA_SP LEFT JOIN USERS US ON US.ID = HD.NHANVIEN_GH LEFT JOIN USERS U ON U.ID = HD.NGUOILAP_HD
	WHERE ' + @P_STR;
END
EXEC SP_EXECUTESQL @SQL
/**************************************
***************************************/
CREATE PROCEDURE REPORT_THEOTIEUCHI_GIAONHAN
(
	@P_THEO_TIEU_CHI NVARCHAR(1)
)
AS
BEGIN
	
	SELECT HD.*, SP.TEN_SP, HS.SOLUONG_DH, US.TAIKHOAN_NV, U.TAIKHOAN_NV AS "NGUOILAP"
	FROM HOADON HD LEFT JOIN HOADON_SANPHAM HS ON HD.MA_HD=HS.MA_HD LEFT JOIN SANPHAM SP ON HS.MA_SP = SP.MA_SP LEFT JOIN USERS US ON US.ID = HD.NHANVIEN_GH LEFT JOIN USERS U ON U.ID = HD.NGUOILAP_HD
	WHERE LOWER(tinhtrang_gh) = @P_THEO_TIEU_CHI;
	
END

/**************************************
***************************************/
CREATE PROCEDURE REPORT_THEOTIEUCHI_THANHTOAN
(
	@P_THEO_TIEU_CHI NVARCHAR(1)
)
AS
BEGIN
	IF @P_THEO_TIEU_CHI = 'n'
	BEGIN
		SELECT HD.*, SP.TEN_SP, HS.SOLUONG_DH, US.TAIKHOAN_NV, U.TAIKHOAN_NV AS "NGUOILAP"
		FROM HOADON HD LEFT JOIN HOADON_SANPHAM HS ON HD.MA_HD=HS.MA_HD LEFT JOIN SANPHAM SP ON HS.MA_SP = SP.MA_SP LEFT JOIN USERS US ON US.ID = HD.NHANVIEN_GH LEFT JOIN USERS U ON U.ID = HD.NGUOILAP_HD
		WHERE LOWER(tinhtrang_tt) = @P_THEO_TIEU_CHI OR tinhtrang_tt is null;
	END
	ELSE IF @P_THEO_TIEU_CHI = 'y'
	BEGIN
		SELECT HD.*, SP.TEN_SP, HS.SOLUONG_DH, US.TAIKHOAN_NV, U.TAIKHOAN_NV AS "NGUOILAP"
		FROM HOADON HD LEFT JOIN HOADON_SANPHAM HS ON HD.MA_HD=HS.MA_HD LEFT JOIN SANPHAM SP ON HS.MA_SP = SP.MA_SP LEFT JOIN USERS US ON US.ID = HD.NHANVIEN_GH LEFT JOIN USERS U ON U.ID = HD.NGUOILAP_HD
		WHERE LOWER(tinhtrang_tt) = @P_THEO_TIEU_CHI;
	END
END
/**************************************
***************************************/

Create PROCEDURE [dbo].[Donhang_By_id]
	@ma_hd nvarchar(50)
AS
	BEGIN
	
		  SELECT HD.*, SP.TEN_SP, HS.SOLUONG_DH, US.TAIKHOAN_NV, U.TAIKHOAN_NV AS "NGUOILAP"
	FROM HOADON HD LEFT JOIN HOADON_SANPHAM HS ON HD.MA_HD=HS.MA_HD LEFT JOIN SANPHAM SP ON HS.MA_SP = SP.MA_SP LEFT JOIN USERS US ON US.ID = HD.NHANVIEN_GH LEFT JOIN USERS U ON U.ID = HD.NGUOILAP_HD
	where HD.ma_hd = @ma_hd
	END
/**************************************
***************************************/
create PROCEDURE dbo.DS_DON_HANG

AS
BEGIN
	SELECT HD.*, US.TAIKHOAN_NV, U.TAIKHOAN_NV AS "NGUOILAP"
	FROM HOADON HD inner JOIN USERS US ON US.ID = HD.NHANVIEN_GH inner JOIN USERS U ON U.ID = HD.NGUOILAP_HD
	
END
/**************************************
***************************************/
create PROCEDURE [dbo].[DS_SP]
	
	@tensp nvarchar(50)
AS
	BEGIN 
		SELECT * FROM sanpham where ten_sp = @tensp
		
	END
	RETURN
/**************************************
***************************************/
create PROCEDURE [dbo].[DS_SP_Byid]
	@id nvarchar(50)
AS
	BEGIN 
		SELECT sp.*, hd_sp.soluong_dh as soluong FROM sanpham sp inner join hoadon_sanpham hd_sp
		on sp.ma_sp = hd_sp.ma_sp and hd_sp.ma_hd = @id ;
		
	END
	RETURN

/**************************************
***************************************/
create PROCEDURE dbo.LayPram
	/*
	(
	@parameter1 int = 5,
	@parameter2 datatype OUTPUT
	)
	*/
	@maparam nvarchar(50)
AS
begin
	select PARAMETER_NAME FROM PARAMETERS where PARAMETER_CODE = @maparam ORDER BY ORDERING
end
	/* SET NOCOUNT ON */
	RETURN
/**************************************
***************************************/
create PROCEDURE dbo.MaSP
	@ten_sp nvarchar(255)
AS
	/* SET NOCOUNT ON */
	begin
	select * from sanpham sp left join hoadon_sanpham hd_sp on sp.ma_sp = hd_sp.ma_sp where ten_sp = @ten_sp
	end
	RETURN
/**************************************
***************************************/
create PROCEDURE [dbo].SuaDH_SP
	
	@ma_hd nvarchar(50),
	@ma_sp nvarchar(50),
	@soluong int
as

BEGIN
		update hoadon_sanpham
			set
			[ma_hd] = @ma_hd, 
			[ma_sp] = @ma_sp,
			[soluong_dh] = @soluong 
		
END
/**************************************
***************************************/
create PROCEDURE [dbo].[SuaDonHang]
	
	@ma_hd nvarchar(50),
	@ten_kh nvarchar(50),
	@sdt_kh nvarchar(25),
	@diachi_kh nvarchar(255),
	@ten_nh nvarchar(50),
	@sdt_nh nvarchar(25),
	@diachi_nh nvarchar(255),
	@nguoilap_hd int,
	@nhanvien_gh int,
	@ngay_dh datetime,
	@ngay_gh bigint,
	@tinhtrang_gh nvarchar(1),
	@tinhtrang_tt nvarchar(1),
	@kieu_cp nvarchar(50),
	@chiphi_cp int,
	@thoigian_cb int,
	@ghichu_hd nvarchar(255),
	@tong_cp int,
	@xacnhan nvarchar(1),
	@tinh_kh nvarchar(255),
	@tinh_nn nvarchar(255),
	@ma_kh nvarchar(50)

as

BEGIN

		update hoadon 
	set
	
		
			[ten_kh] =@ten_kh,
			[sdt_kh]=@sdt_kh, 
			[diachi_kh]=@diachi_kh,
			[ten_nh]=@ten_nh,
			[sdt_nh]= @sdt_nh,
			[diachi_nh]= @diachi_nh,
			[nguoilap_hd]= @nguoilap_hd,
			[nhanvien_gh]= @nhanvien_gh,
			[ngay_dh]= @ngay_dh,
			[ngay_gh]= @ngay_gh,
			[tinhtrang_gh]= @tinhtrang_gh,
			[tinhtrang_tt]= @tinhtrang_tt,
			kieu_cp= @kieu_cp,
			chiphi_cp = @chiphi_cp,
			thoigian_cb = @thoigian_cb,
			ghichu_hd = @ghichu_hd,
			tong_cp= @tong_cp,
			xacnhan = @xacnhan,
			tinh_kh = @tinh_kh,
			tinh_nn = @tinh_nn,
			ma_kh = @ma_kh
			
		
			 where 	[ma_hd] = @ma_hd
	
END
/**************************************
***************************************/
create PROCEDURE dbo.TenSP
	
AS
	begin
		select ten_sp from sanpham 
	end
	RETURN
/**************************************
***************************************/

create PROCEDURE [dbo].ThemDH_SP
	
	@ma_hd nvarchar(50),
	@ma_sp nvarchar(50),
	@soluong int
as

BEGIN
		INSERT INTO hoadon_sanpham
			(
			[ma_hd], 
			[ma_sp],
			[soluong_dh] 
			)
		VALUES
			(
			@ma_hd,
			@ma_sp,
			@soluong 
			)
			update sanpham
			set soluong_cl = soluong_cl - @soluong
			where ma_sp = @ma_sp 
		
			
END
/**************************************
***************************************/
create PROCEDURE [dbo].ThemDonHang
	
	@ma_hd nvarchar(50),
	@ma_kh nvarchar(50),
	@ten_kh nvarchar(50),
	@sdt_kh nvarchar(25),
	@diachi_kh nvarchar(255),
	@ten_nh nvarchar(50),
	@sdt_nh nvarchar(25),
	@diachi_nh nvarchar(255),
	@nguoilap_hd int,
	@nhanvien_gh int,
	@ngay_dh datetime,
	@ngay_gh bigint,
	@tinhtrang_gh nvarchar(1),
	@tinhtrang_tt nvarchar(1),
	@kieu_cp nvarchar(50),
	@chiphi_cp int,
	@thoigian_cb int,
	@ghichu_hd nvarchar(255),
	@tong_cp int,
	@xacnhan nvarchar(1),
	@tinh_kh nvarchar(255),
	@tinh_nn nvarchar(255)

as

BEGIN

		INSERT INTO hoadon
			(
			[ma_hd], 
			ma_kh,
			[ten_kh],
			[sdt_kh], 
			[diachi_kh],
			[ten_nh],
			[sdt_nh],
			[diachi_nh],
			[nguoilap_hd],
			[nhanvien_gh],
			[ngay_dh],
			[ngay_gh],
			[tinhtrang_gh],
			[tinhtrang_tt],
			kieu_cp,
			chiphi_cp ,
			thoigian_cb ,
			ghichu_hd,
			tong_cp,
			xacnhan,
			tinh_kh,
			tinh_nn
			 
			
			 )
		VALUES
			(
			@ma_hd,
			@ma_kh,
			@ten_kh,
			@sdt_kh,
			@diachi_kh,
			@ten_nh,
			@sdt_nh,
			@diachi_nh,
			@nguoilap_hd,
			@nhanvien_gh,
			@ngay_dh,
			@ngay_gh,
			@tinhtrang_gh,
			@tinhtrang_tt ,
			@kieu_cp,
			@chiphi_cp ,
			@thoigian_cb ,
			@ghichu_hd, 
			@tong_cp,
			@xacnhan,
			@tinh_kh,
			@tinh_nn
			
			)
END

/**************************************
***************************************/
create PROCEDURE dbo.timKiem
	/*
	(
	@parameter1 int = 5,
	@parameter2 datatype OUTPUT
	)
	*/
	@whereCondition nvarchar(500)
	

	 AS
BEGIN
 SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(max)
set @sql = 'SELECT HD.*, US.TAIKHOAN_NV, U.TAIKHOAN_NV AS "NGUOILAP"
	FROM HOADON HD inner JOIN USERS US ON US.ID = HD.NHANVIEN_GH inner JOIN USERS U ON U.ID = HD.NGUOILAP_HD '+ @whereCondition
EXEC sp_executesql @SQL
END

/**************************************
***************************************/

create PROCEDURE dbo.xoaDH_SP
	/*
	(
	@parameter1 int = 5,
	@parameter2 datatype OUTPUT
	)
	*/
	@ma_hd nvarchar(50)
AS
	/* SET NOCOUNT ON */
	begin
		delete from hoadon_sanpham where ma_hd = @ma_hd; 
	end
	RETURN

















